"""
Copyright (c) 2023 - present SKbioinfo
"""
from apps.NGS_API.connector.db_common import query_info
from apps.NGS_API.common.db_table_config import *
from flask_restful import reqparse,Resource
from flask_login import login_required
from flask import jsonify,request

from apps.NGS_API.utils.log_helper import lg

def time_range_query(table_name:str,cols:str,or_col:str,range_col:str,start_time:str,end_time:str):
    search_sql=""
    search_sql = '''
        SELECT 
            {cols}
        FROM {table_name}
        WHERE {range_col} BETWEEN '{start_time}' AND '{end_time}' 
        ORDER BY {or_col} DESC
    '''.format(
        cols=cols,
        table_name=table_name,
        or_col=or_col,
        range_col=range_col,
        start_time=start_time,
        end_time=end_time
    )
    return search_sql

def base_info_query(table_name:str,cols:str,or_col:str,limit:int):
    search_sql=""
    search_sql= '''
        SELECT
            {cols}
        FROM {table_name}
        ORDER BY {or_col} DESC
        LIMIT {limit}
    '''.format(
        cols=cols,
        table_name=table_name,
        or_col=or_col,
        limit=limit
    )
    return search_sql

class interfaceProjectTimePicker(Resource):
    @login_required
    def get(self):
        try:
            projects={}
            ### SQL: project_base_info_sql ###
            cols = '''
                primary_id,
                client_id,
                product_id,
                protocol_id,
                sample_receive_id,
                project_type,
                project_level,
                create_user,
                incharge_user,
                create_time,
                modify_time,
                id,
                status                  
            '''
            project_base_info_sql = base_info_query(
                cols=cols,
                table_name="pms_project",
                or_col = "create_time",
                limit = 5   
            )
            project_base_info = query_info(project_base_info_sql)
            project_infos=[]
            ### rename project info columns ###
            for project in project_base_info:
                project_infos.append({
                    "id": project[11],
                    "proj_id": project[0],
                    "cli_id": project[1],
                    "prod_id": project[2],
                    "prot_id": project[3],
                    "sam_rec_id": project[4],
                    "proj_ty": project[5],
                    "proj_le": project[6],
                    "cre_user": project[7],
                    "in_user": project[8],
                    "cre_time": project[9],
                    "modify_time": project[10],
                    "status": project[12]
                })
            ### update project status ###
            for i in range(0,len(project_infos)):
                if int(project_infos[i]["status"]) == 1:
                    project_infos[i].update({"status":"完成"})
                elif int(project_infos[i]["status"]) == 0:
                    project_infos[i].update({"status":"进行中"})
            projects["project_infos"] = project_infos
            response=jsonify(projects)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)
            
    @login_required
    def post(self):
        ### parse time range picker ###
        try:
            projects = {}
            parser = reqparse.RequestParser()
            parser.add_argument('start_time',type=str,required=True,help="起始日期验证失败")
            parser.add_argument('end_time',type=str,required=True,help="结束日期验证失败")
            args=parser.parse_args()
            ### SQL: query time range infos of project ###
            cols='''
                primary_id,
                client_id,
                product_id,
                protocol_id,
                sample_receive_id,
                project_type,
                project_level,
                create_user,
                incharge_user,
                create_time,
                modify_time,
                id,
                status
            ''' 
            search_project_sql=time_range_query(
                    cols = cols,
                    table_name=project_db["name"],
                    range_col="create_time",
                    or_col="create_time",
                    start_time = args["start_time"],
                    end_time = args["end_time"]
            )
            query_projects = query_info(search_project_sql)
            ### collect project infos ###
            project_infos=[]
            ### rename project info columns ### 
            for project in query_projects:
                project_infos.append({
                    "id": project[11],
                    "proj_id": project[0],
                    "cli_id": project[1],
                    "prod_id": project[2],
                    "prot_id": project[3],
                    "sam_rec_id": project[4],
                    "proj_ty":project[5],
                    "proj_le":project[6],
                    "cre_user":project[7],
                    "in_user":project[8],
                    "cre_time":project[9],
                    "modify_time":project[10],
                    "status": project[12]
                })
            projects["project_infos"] = project_infos
            response=jsonify(projects)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)

class interfaceSampleTimePicker(Resource):
    @login_required
    def get(self):
        try:
            samples={}
            ### SQL: project_base_info_sql ###
            cols = '''
                primary_sample_id,
                primary_project_id,
                operate_user,
                check_user,
                sample_source,
                sample_type,
                sample_name,
                init_amount,
                cur_amount,
                create_time,
                modify_time,
                id        
            '''
            sample_base_info_sql = base_info_query(
                cols=cols,
                table_name=sample_db["name"],
                or_col = "create_time",
                limit = 5   
            )
            sample_base_info = query_info(sample_base_info_sql)
            sample_infos=[]
            ### rename project info columns ###
            for sample in sample_base_info:
                sample_infos.append({
                    "id": sample[11],
                    "sam_id": sample[0],
                    "proj_id": sample[1],
                    "op_user": sample[2],
                    "ch_user": sample[3],
                    "sam_source": sample[4],
                    "sam_type": sample[5],
                    "sam_name": sample[6],
                    "init_amount": sample[7],
                    "cur_amount": sample[8],
                    "cre_time": sample[9],
                    "md_time": sample[10]
                })
            samples["sample_infos"] = sample_infos
            response=jsonify(samples)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)
            
    @login_required
    def post(self):
        try:
            samples = {}
            parser = reqparse.RequestParser()
            parser.add_argument('start_time',type=str,required=True,help="起始日期验证失败")
            parser.add_argument('end_time',type=str,required=True,help="结束日期验证失败")
            args=parser.parse_args()
            ### SQL: query time range infos of sample ###
            cols='''
                primary_sample_id,
                primary_project_id,
                operate_user,
                check_user,
                sample_source,
                sample_type,
                sample_name,
                init_amount,
                cur_amount,
                create_time,
                modify_time,
                id,
                sample_id
            '''
            search_sample_sql=time_range_query(
                cols=cols,
                table_name=sample_db["name"],
                range_col="create_time",
                or_col="create_time",
                start_time=args["start_time"],
                end_time=args["end_time"]
            )
            ### SQL: query time range infos of samples ###
            query_samples = query_info(search_sample_sql)
            sample_infos=[]
            for sample in query_samples:
                sample_infos.append({
                    "id": sample[11],
                    "sam_id": sample[0],
                    "proj_id": sample[1],
                    "op_user": sample[2],
                    "ch_user": sample[3],
                    "sam_source": sample[4],
                    "sam_type": sample[5],
                    "sam_name": sample[6],
                    "init_amount": sample[7],
                    "cur_amount": sample[8],
                    "cre_time": sample[9],
                    "md_time": sample[10],
                    "sys_sam_id": sample[11]
                })
            samples["sample_infos"] = sample_infos
            response=jsonify(samples)
            response.status_code = 202
            return response
        except Exception as e:
            lg.error(e)